#从数据库中读取经纬度，并绘制地图
#读取数据库部分
from sqlalchemy import create_engine
engine = create_engine('mysql+pymysql://route:route@localhost:3306/edu_ip?charset=utf8')
conn = engine.connect()
f = open("./result/ipv6_school_s2d_change.txt",'r',encoding='utf8')

#绘制地图部分
from pyecharts import options as opts
from pyecharts.charts import Geo
from pyecharts.globals import ChartType, SymbolType
geo = Geo(init_opts = {"width":1600,"title":"教育网拓扑"})
geo.add_schema(maptype="china")

t = []
line = []
i = 1
while True:
    school = f.readline().replace('\n','')
    if len(school)==0:
        break
    else:
        school_list = school.split(',')

        if school_list[0] not in t:
            t.append(school_list[0])
            cur = engine.execute(
                "SELECT longitude,latitude FROM college WHERE name like '"+school_list[0]+"'"
            )
            coor = cur.fetchone()
            geo.add_coordinate(school_list[0],coor[0],coor[1])
            geo.add(school_list[0], [(school_list[0], i)])
            i += 1
            cur.close()

        if school_list[1] not in t:
            t.append(school_list[1])
            cur = engine.execute(
                "SELECT longitude,latitude FROM college WHERE name like '"+school_list[1]+"'"
            )
            coor = cur.fetchone()
            geo.add_coordinate(school_list[1],coor[0],coor[1])
            geo.add(school_list[1], [(school_list[1], i)])
            i += 1
            cur.close()
        
        line.append(tuple([school_list[0],school_list[1]]))

f.close()
conn.close()
geo.add('',line,effect_opts=opts.EffectOpts(symbol=SymbolType.ARROW,symbol_size=5, color="blue"),\
    linestyle_opts=opts.LineStyleOpts(type_="dashed",curve=0.2,color='rgb(128, 128, 128)'),type_=ChartType.LINES)
geo.set_series_opts(label_opts=opts.LabelOpts(is_show=False))
geo.set_series_opts(legend_opts=opts.LegendOpts(is_show=False))
geo.set_global_opts(legend_opts=opts.LegendOpts(is_show=False))
geo.render()